﻿using Corina.Datamodel.Base;
using Corina.Enumerations;
using Corina.Infrastructure;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Corina.Utilities;
using System.Globalization;

namespace Corina.DataContext.DatabaseOperator
{
    public class DatabaseOperatorBase : BaseClass
    {

        public object InsertEntity(string connectionString, DatamodelEntityBase entity)
        {
            string queryString = entity.SqlInsertString + " SELECT @@identity as Id";

            List<SqlParameter> paras = new List<SqlParameter>();

            entity.Columns().ForEach(t =>
            {
                paras.Add(new SqlParameter("@" + t.ColumnName, entity.GetPropertyValue(t.ColumnName)));
            });

            object value = SqlHelper.ExecuteScalar(connectionString, CommandType.Text, queryString, paras.ToArray());

            return value;
        }

        public bool UpdateEntityById(string connectionString, int id, string tableName, List<string> properties, List<object> values)
        {
            List<SqlParameter> paras = new List<SqlParameter>();
            List<string> setted = new List<string>();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("UPDATE {0} SET ", tableName);
            for (var i = 0; i < properties.Count; i++)
            {
                string columnName = properties[i];

                setted.Add(string.Format(" {0}=@{0}", columnName));

                paras.Add(new SqlParameter("@" + columnName, values[i]));
            }
            builder.Append(string.Join(",", setted.ToArray()));
            builder.AppendFormat(" WHERE Id = {0}", id);

            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, builder.ToString(), paras.ToArray()) > 0;
        }

        public bool UpdateEntityById<T>(string connectionString, int id, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            return UpdateEntity<T>(connectionString, "Id", id, properties, values);
        }

        public bool UpdateEntity(string connectionString, DatamodelEntityBase entity, EntityOperation operation)
        {
            string queryString = string.Empty;
            List<SqlParameter> paras = new List<SqlParameter>();
            switch (operation)
            {
                case EntityOperation.Delete:
                    queryString = entity.SqlDeleteString;
                    paras.Add(new SqlParameter("@Id", entity.GetPropertyValue("Id")));
                    break;
                case EntityOperation.Insert:
                    queryString = entity.SqlInsertString;
                    paras = DatabaseCommon.GetParameterFromEntity(entity);
                    break;
                case EntityOperation.Update:
                    queryString = entity.SqlUpdateString;
                    paras = DatabaseCommon.GetParameterFromEntity(entity);
                    break;
            }

            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, queryString, paras.ToArray()) > 0;
        }

        public bool UpdateEntity<T>(string connectionString,string primaryKey,object value, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            T entity = new T();

            List<SqlParameter> paras = new List<SqlParameter>();
            List<string> setted = new List<string>();
            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("UPDATE {0} SET ", entity.TableName);
            for (var i = 0; i < properties.Count; i++)
            {
                string columnName = properties[i];

                setted.Add(string.Format(" {0}=@{0}", columnName));

                paras.Add(new SqlParameter("@" + columnName, values[i]));
            }
            builder.Append(string.Join(",", setted.ToArray()));
            builder.AppendFormat(" WHERE {0} = @Value", primaryKey);
            paras.Add(new SqlParameter("@Value", value));

            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, builder.ToString(), paras.ToArray()) > 0;
        }

        public bool UpdateEntity(string connectionString, string commandString, params SqlParameter[] paras)
        {
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, commandString, paras) > 0;
        }

        public bool RemoveEntityById<T>(string connectionString, int id) where T : DatamodelEntityBase, new()
        {
            T entity = new T();
            string queryString = entity.SqlDeleteString;
            SqlParameter para = new SqlParameter("@Id", id);

            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, queryString, para) > 0;
        }

        public bool RemoveEntityById(string connectionString, string tableName, int id)
        {
            string queryString = string.Format("DELETE FROM {0} WHERE Id = @Id", tableName);

            SqlParameter para = new SqlParameter("@Id", id);

            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, queryString, para) > 0;
        }

        public bool RemoveEntity(string connectionString, DatamodelEntityBase entity)
        {
            string queryString = entity.SqlDeleteString;
            SqlParameter para = new SqlParameter("@Id", entity.GetPropertyValue("Id"));

            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, queryString, para) > 0;
        }

        public bool Exists(string connectionString, string tablename, List<string> properties, List<object> values)
        {
            StringBuilder builder = new StringBuilder();
            List<SqlParameter> paras = new List<SqlParameter>();
            builder.AppendFormat("SELECT Id FROM {0} WHERE 1=1 ", tablename);

            for (var i = 0; i < properties.Count; i++)
            {
                string columnName = properties[i];

                builder.AppendFormat(" AND {0}=@{0} ", columnName);

                paras.Add(new SqlParameter("@" + columnName, values[i]));
            }

            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, builder.ToString(), paras.ToArray()).HasRows;
        }

        public bool Exists<T>(string connectionString, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            StringBuilder builder = new StringBuilder();
            List<SqlParameter> paras = new List<SqlParameter>();
            builder.AppendFormat("SELECT Id FROM {0} WHERE 1=1 ", new T().TableName);

            for (var i = 0; i < properties.Count; i++)
            {
                string columnName = properties[i];

                builder.AppendFormat(" AND {0}=@{0} ", columnName);

                paras.Add(new SqlParameter("@" + columnName, values[i]));
            }

            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, builder.ToString(), paras.ToArray()).HasRows;
        }

        public DataTable GetDataTable<T>(string connectionString, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            DataTable table = new DataTable();
            List<SqlParameter> paras = new List<SqlParameter>();

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("{0} WHERE 1=1 ", new T().SqlQueryString);

            for (var i = 0; i < properties.Count; i++)
            {
                string columnName = properties[i];

                builder.AppendFormat(" AND {0}=@{0} ", columnName);

                paras.Add(new SqlParameter("@" + columnName, values[i]));
            }

            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, builder.ToString(), paras.ToArray());
            table.Load(reader);
            return table;
        }

        //public DataTable GetDataTable<T>(string connectionString, params QueryOperationInfo[] queries) where T : DatamodelEntity, new()
        //{
        //    DataTable table = new DataTable();
        //    List<SqlParameter> paras = new List<SqlParameter>();

        //    StringBuilder builder = new StringBuilder();
        //    builder.AppendFormat("{0} WHERE 1=1 ", new T().SqlQueryString);

        //    for (var i = 0; i < queries.Length; i++)
        //    {
        //        QueryOperationInfo query = queries[i];

        //        paras.Add(new SqlParameter("@" + query.Parameter, query.Value));

        //        builder.Append(query.ParamString);
        //    }

        //    SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, builder.ToString(), paras.ToArray());
        //    table.Load(reader);
        //    return table;
        //}

        public DataTable GetDataTable(string connectionString, string commandString, params SqlParameter[] paras)
        {
            DataTable table = new DataTable();
            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, commandString, paras);
            table.Load(reader);
            return table;
        }

        public List<T> GetEntities<T>(string connectionString, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            return GetDataTable<T>(connectionString, properties, values).ToList<T>();
        }

        public List<T> GetEntities<T>(string connectionString, string commandString, params SqlParameter[] paras) where T : DatamodelEntityBase, new()
        {
            return GetDataTable(connectionString, commandString, paras).ToList<T>();
        }

        public T GetEntity<T>(string connectionString, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            List<T> entities = GetEntities<T>(connectionString, properties, values);
            return entities.Count > 0 ? entities.FirstOrDefault() : null;
        }

        public T GetEntity<T>(string connectionString, string commandString, params SqlParameter[] paras) where T : DatamodelEntityBase, new()
        {
            List<T> entities = GetEntities<T>(connectionString, commandString, paras);
            return entities.Count > 0 ? entities.FirstOrDefault() : null;
        }

        public int GetCount(string connectionString, string commandString, params SqlParameter[] paras)
        {
            DataTable table = new DataTable();
            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, commandString, paras);
            table.Load(reader);
            if (table == null || table.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(table.Rows[0][0]);
            }
        }

        public int GetEntitiesCount<T>(string connectionString, List<string> properties, List<object> values) where T : DatamodelEntityBase, new()
        {
            DataTable table = new DataTable();
            List<SqlParameter> paras = new List<SqlParameter>();

            StringBuilder builder = new StringBuilder();
            builder.AppendFormat("SELECT COUNT(*) FROM {0} WHERE 1=1 ", new T().TableName);

            for (var i = 0; i < properties.Count; i++)
            {
                string columnName = properties[i];

                builder.AppendFormat(" AND {0}=@{0} ", columnName);

                paras.Add(new SqlParameter("@" + columnName, values[i]));
            }

            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, builder.ToString(), paras.ToArray());
            table.Load(reader);
            if (table == null || table.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(table.Rows[0][0]);
            }
        }

        public int GetEntitiesCount<T>(string connectionString, string commandString, params SqlParameter[] paras) where T : DatamodelEntityBase, new()
        {
            DataTable table = new DataTable();
            SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, commandString, paras);
            table.Load(reader);
            if (table == null || table.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(table.Rows[0][0]);
            }
        }

        public int ExexuteCommand(string connectionString, List<string> commands, params SqlParameter[] paras)
        {
            int count = 0;
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            try
            {
                commands.ForEach(t =>
                {
                    count += SqlHelper.ExecuteNonQuery(tran, CommandType.Text, t, paras);
                });
                tran.Commit();
            }
            catch
            {
                tran.Rollback();
            }
            connection.Close();
            return count;
        }

        public bool InsertEntities(string connectionString, List<DatamodelEntityBase> entities)
        {
            DatamodelEntityBase baseentity = entities.FirstOrDefault();
            string queryString = baseentity.SqlQueryString + " WHERE 1=2 ";
            DataTable table = GetDataTable(connectionString, queryString);

            entities.ForEach((t) =>
            {
                DataRow row = table.NewRow();

                foreach (var item in t.Columns().Where(a => !a.IsPrimaryKey))
                {
                    if (t.IsEnumProperty(item.ColumnName))
                    {
                        row[item.ColumnName] = Convert.ToInt32(t[item.ColumnName]);
                    }
                    else
                    {
                        Type type = t.PropertyType(item.ColumnName);
                        row[item.ColumnName] = Convert.ChangeType(t[item.ColumnName], type, CultureInfo.CurrentCulture);
                    }
                    //row[item.ColumnName] = t.IsEnumProperty(item.ColumnName) ? Convert.ToInt32(t[item.ColumnName]) : t[item.ColumnName];
                }

                table.Rows.Add(row);
            });

            return SqlHelper.BulkToDB(connectionString, table, baseentity.TableName);
        }

        public bool InsertEntities<T>(string connectionString, List<T> entities) where T : DatamodelEntityBase, new()
        {
            DatamodelEntityBase baseentity = new T();
            string queryString = baseentity.SqlQueryString + " WHERE 1=2 ";
            DataTable table = GetDataTable(connectionString, queryString);

            entities.ForEach((t) =>
            {
                DataRow row = table.NewRow();

                foreach (var item in t.Columns().Where(a => !a.IsPrimaryKey))
                {
                    if (t.IsEnumProperty(item.ColumnName))
                    {
                        row[item.ColumnName] = Convert.ToInt32(t[item.ColumnName]);
                    }
                    else
                    {
                        Type type = t.PropertyType(item.ColumnName);
                        row[item.ColumnName] = Convert.ChangeType(t[item.ColumnName], type, CultureInfo.CurrentCulture);
                    }
                    //row[item.ColumnName] = t.IsEnumProperty(item.ColumnName) ? Convert.ToInt32(t[item.ColumnName]) : t[item.ColumnName];
                }

                table.Rows.Add(row);
            });

            return SqlHelper.BulkToDB(connectionString, table, baseentity.TableName);
        }
    }
}
